/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package be.hogent.iii.project.server.databank;

import be.hogent.iii.project.common.game.objecten.Resultaat;
import be.hogent.iii.project.common.game.objecten.Speler;
import be.hogent.iii.project.common.game.objecten.Wedstrijd;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;
import java.util.ResourceBundle;



/**
 *
 * @author jef donders
 */
public class DataStorage {

    private ResourceBundle constanten;
    
    public DataStorage() {
        constanten = ResourceBundle.getBundle("be.hogent.iii.project.server.databank.databankconstanten");
    }

    private Connection geefVerbinding() throws SQLException {

        Connection conn = null;
        try {
             // MySQL MM JDBC driver 
            //String driverName = "com.mysql.jdbc.Driver"; // MySQL MM JDBC driver
            Class.forName(constanten.getString("driver"));
            String url = constanten.getString("connectiestring");
            String username = constanten.getString("gebruiker");
            String password = constanten.getString("wachtwoord");
            System.out.println("aanamken connectie");
            return DriverManager.getConnection(url, username, password);
        } catch (Exception ex) {
            System.out.println("Connectie aanmaken mislukt\n" + "error: " + ex.getMessage());
            ex.printStackTrace();
        }
        return conn;

    }

    public void geefSpelers() {
        try {
            Connection conn = geefVerbinding();
            try {
                Statement stmt = conn.createStatement();
                try {
                    ResultSet rs = stmt.executeQuery(constanten.getString("getSpelers"));
                    while (rs.next()) {
                        System.out.println(rs.getString("nickname") + " " +
                                rs.getString("password") + " " +
                                rs.getString("email") + " " +
                                rs.getInt("wincount") + " " +
                                rs.getInt("losscount"));
                    }
                } finally {
                    stmt.close();
                }
            } finally {
                conn.close();
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }

    }

    public Speler login(String nickname, char[] wachtw) {
        String wachtwoord = new String(wachtw);
        Speler speler = null;
        try {
            Connection conn = geefVerbinding();
            try {
                PreparedStatement pstmt = conn.prepareStatement(constanten.getString("getSpeler"));
                try {
                    pstmt.setString(1, nickname);
                    ResultSet rs = pstmt.executeQuery();
                    if (rs.next() && rs.getString("password").equalsIgnoreCase(wachtwoord)) {
                        speler = new Speler();
                        speler.setEmail(rs.getString("email"));
                        speler.setNick(rs.getString("nickname"));
                        speler.setWincount(rs.getInt("wincount"));
                        speler.setLosscount(rs.getInt("losscount"));
                    }
                } finally {
                    pstmt.close();
                }
            } finally {
                conn.close();
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return speler;
    }

    public Speler geefSpeler(String nickname) {
        Speler speler = null;
        try {
            Connection conn = geefVerbinding();
            try {
                PreparedStatement pstmt = conn.prepareStatement(constanten.getString("getSpeler"));
                try {
                    pstmt.setString(1, nickname);
                    ResultSet rs = pstmt.executeQuery();
                    if (rs.next()) {
                        speler = new Speler();
                        speler.setEmail(rs.getString("email"));
                        speler.setNick(rs.getString("nickname"));
                        speler.setWincount(rs.getInt("wincount"));
                        speler.setLosscount(rs.getInt("losscount"));
                    }
                } finally {
                    pstmt.close();
                }
            } finally {
                conn.close();
            }
        } catch (Exception e) {
            System.out.println("error bij geef spelers\n" + e.getMessage());
            e.printStackTrace();
        }
        return speler;
    }

    public Boolean maakSpeler(String nickname, char[] wachtw, String emailadres) {
        String wachtwoord = new String(wachtw);
        try {
            Connection conn = geefVerbinding();
            try {
                PreparedStatement pstmt = conn.prepareStatement(constanten.getString("insertSpeler"));
                Statement stmt = conn.createStatement();
                try {
                    pstmt.setString(1, nickname);
                    pstmt.setString(2, wachtwoord);
                    pstmt.setString(3, emailadres);
                    pstmt.executeUpdate();
                } finally {
                    stmt.close();
                }
            } finally {
                conn.close();
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return true;
    }

    public boolean veranderWachtwoord(String nickname, char[] nieuw){
        String nieuwWachtwoord = new String(nieuw);
        try {
            Connection conn = geefVerbinding();
            try {
                PreparedStatement pstmt = conn.prepareStatement(constanten.getString("veranderPassword"));
                try {
                    pstmt.setString(1, nieuwWachtwoord);
                    pstmt.setString(2, nickname);
                    pstmt.executeUpdate();
                } finally {
                    pstmt.close();
                }
            } finally {
                conn.close();
            }
        } catch (Exception e) {
            System.out.println("error bij geef spelers\n" + e.getMessage());
            e.printStackTrace();
        }  
        return true;
    }
    
    public String geefMail(String nickname) {
        String email = "";
        try {
            Connection conn = geefVerbinding();
            try {
                PreparedStatement pstmt = conn.prepareStatement(constanten.getString("getEmail"));
                try {
                    pstmt.setString(1, nickname);
                    ResultSet rs = pstmt.executeQuery();
                    if (rs.next()) {
                        email = rs.getString("email");
                    }
                } finally {
                    pstmt.close();
                }
            } finally {
                conn.close();
            }
        } catch (Exception e) {
            System.out.println("error bij geef email\n" + e.getMessage());
            e.printStackTrace();
        }
        return email;
    }

    public Boolean maakBattle( ArrayList<String> winnaars, ArrayList<String> verliezers, Date datum, Time lengte, String mapnaam) {
        try {
            Connection conn = geefVerbinding();
            try {
                PreparedStatement pstmt = conn.prepareStatement(constanten.getString("insertBattle"));
                try {
                    pstmt.setString(1, mapnaam);
                    pstmt.setTime(2, lengte);
                    pstmt.setDate(3, datum);
                    int result = pstmt.executeUpdate();
                    System.out.println("resultaat :" + result);

                    Statement stmt = conn.createStatement();
                    try {
                        int bid = 0;
                        ResultSet rs = stmt.executeQuery(constanten.getString("getLaatsteBattle"));
                        if (rs.next()) {
                            bid = rs.getInt("bid");
                        }
                        voegResultsToe(bid, verliezers, winnaars);
                    } finally {
                        stmt.close();
                    }

                } finally {
                    pstmt.close();
                }
            } finally {
                conn.close();
            }


        } catch (Exception e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return true;
    }

    public Boolean voegResultsToe(int bid, ArrayList<String> verliezers, ArrayList<String> winnaars) {
        boolean result = true;
        for(int i=0;i<verliezers.size();i++){
            if ( !voegResultToe(bid, verliezers.get(i),0))
                result = false;
        }
        for(int i=0;i<winnaars.size();i++){
            if (!voegResultToe(bid, winnaars.get(i),1))
                result = false;
        }
        return result;
    }
    
    public Boolean voegResultToe(int bid, String speler, int result) {
        try {
            Connection conn = geefVerbinding();
            try {
                PreparedStatement pstmt = conn.prepareStatement(constanten.getString("insertResult"));
                Statement stmt = conn.createStatement();
                try {
                    pstmt.setInt(1, bid);
                    pstmt.setString(2, speler);
                    pstmt.setInt(3, result);
                    pstmt.executeUpdate();
                } finally {
                    stmt.close();
                }
            } finally {
                conn.close();
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return true;
    }
    
    public ArrayList<Wedstrijd> getOverzichtWedstrijden(String nickname) {
        ArrayList<Wedstrijd> wedstrijden = new ArrayList<Wedstrijd>();
        try {
            Connection conn = geefVerbinding();
            try {
                /*String sqlstatement = "select b.bid, b.mapname, b.matchlength, b.date, r2.nickname " +
                        "from results r " +
                        "join battles b on b.bid = r.bid " +
                        "join results r2 on r2.bid = b.bid " +
                        "where r.nickname = ? and r2.result = 1 ";*/
                String sqlstatement = "select b.bid, b.mapname, b.matchlength, b.date, r.result " +
                        "from results r " +
                        "join battles b on b.bid = r.bid " +
                        "where r.nickname = ?";
                PreparedStatement pstmt = conn.prepareStatement(sqlstatement);
                try {
                    pstmt.setString(1, nickname);
                    ResultSet rs = pstmt.executeQuery();
                    while (rs.next()) {
                        Wedstrijd wedstrijd = new Wedstrijd();
                        wedstrijd.setBattleId(rs.getInt("b.bid"));
                        wedstrijd.setDatum(rs.getString("b.date"));
                        wedstrijd.setSpeelduur(rs.getString("b.matchlength"));
                        wedstrijd.setMapname(rs.getString("b.mapname"));
                        if (rs.getInt("r.result") == 1)
                            wedstrijd.setGewonnen(true);
                        else
                            wedstrijd.setGewonnen(false);
                        wedstrijden.add(wedstrijd);
                    }
                } finally {
                    pstmt.close();
                }
            } finally {
                conn.close();
            }
        } catch (Exception e) {
            System.out.println("error bij geef email\n" + e.getMessage());
            e.printStackTrace();
        }
        return wedstrijden;
    }

    public Resultaat getOverzichtWedstrijd(int battleId) {
        Resultaat result = null;
        try {
            Connection conn = geefVerbinding();
            try {
                PreparedStatement pstmt = conn.prepareStatement(constanten.getString("getResultsBattle"));
                try {
                    result = new Resultaat();
                    pstmt.setInt(1, battleId);
                    ResultSet rs = pstmt.executeQuery();
                    ArrayList<String> verliezers = new ArrayList<String>();
                    ArrayList<String> winnaars = new ArrayList<String>();
                    while (rs.next()) {
                        int gewonnen = rs.getInt("result");
                        String nick = rs.getString("nickname");
                        if (gewonnen == 1) {
                            winnaars.add(nick);
                        } else if (gewonnen == 0) {
                            verliezers.add(nick);
                        }
                    }
                    result.setVerliezers(verliezers);
                    result.setWinnaars(winnaars);
                } finally {
                    pstmt.close();
                }
            } finally {
                conn.close();
            }
        } catch (Exception e) {
            System.out.println("error bij geef email\n" + e.getMessage());
            e.printStackTrace();
        }
        return result;
    }
}
